const connection = require('../app/database')
class pushcontent {
  //获得文章
  async gettext(l,m) {
    const statement =	
    `	
    SELECT  JSON_OBJECT('uid',a.id,'type',a.type,'content',a.content, 'like',l.alike, 'title',a.title,'userid',u.id,'createtm',a.createAt,'updatatm',a.updateAt,'name',u.name,'reward',a.reward) essay from article a
    LEFT JOIN user u on a.user_id = u.id
    LEFT JOIN alike  l on a.id = l.article_id
    LIMIT ?,?;
  `
    const res = await connection.execute(statement, [l, m])
    return res[0]
  }
  //创建 文章
  async create(valueHtml,title,type,id,reward) {
    const statement = `INSERT INTO article(content,user_id,title,type,reward) VALUES(?,?,?,?,?);`
    if(type == true) { 
      type = 1
    }else {
      type = 0
    }
    try {
      const res = await connection.execute(statement, [valueHtml,id,title,type,reward])
      return res
    } catch (error) {
        console.log(error);
    }
  }
  async textdelete(user_id, id) {
    const statement = `DELETE FROM content WHERE user_id = ?;`
    const res = await connection.execute(statement,[user_id, id])
    return res
  }
  async putarticle(aid,userid) {
    const statement =   `UPDATE alike SET	alike = alike + 1  WHERE article_id = ?`
    const res = await connection.execute(statement,[aid])
    const statement1 = `INSERT INTO state(userid_like_article,article_id) VALUES(?,?)`
    const resz = await connection.execute(statement1,[userid,aid])
    return {...res,...resz}
  }
  async articlereply(valueHtml,articleid,id){
      const statement  = `INSERT into reply(reply_text,article_id,s_id) VALUES (?,?,?)`
      const res = await connection.execute(statement,[valueHtml,articleid,id])
      return res
  }
  async getarticlereply(articleid,l,m){
    const statement =  `	SELECT r.reply_text text , r.createAt ,r.updateAt , u.name  ,u.avatar_url url from reply r LEFT JOIN user u on r.s_id = u.id WHERE r.article_id = ? LIMIT ?,?`
    const res = await connection.execute(statement,[articleid,l,m])
    return res[0]
  }
  async getallarticlereply(articleid) {
    const statement = `SELECT r.reply_text text,r.createAt,r.updateAt,u.name,u.avatar_url url FROM reply r LEFT JOIN USER u ON r.s_id = u.id WHERE r.article_id = ?`
    const res = await connection.execute(statement,[articleid])
    return res[0]
  }
  async getcomment (articleid) {
    const statement = `SELECT  c.user_id id, c.article_id articleid, c.id commentid,c.text,c.createAt,c.updateAt,u.name,u.avatar_url url,JSON_ARRAYAGG(JSON_OBJECT('id',user.id,"text",ch.text,'name',user.name,'url',user.avatar_url,'commentuid',ch.comment_id,'createAt',ch.createAt)) children
    from comment c  
    LEFT JOIN user u on c.user_id = u.id 
    LEFT JOIN childrencomment ch on comment_id = c.id
    LEFT JOIN user on ch.user_id = user.id
    GROUP BY c.id HAVING c.article_id = ?
     `
     try {
    const res = await connection.execute(statement,[articleid])
    return res[0]
     } catch (re) {
      console.log(re);    
    }
  }
  //添加评论 
  async setcomment(commentid, userid, zzycomment, articleid) {
    let statement = ''
    console.log(commentid, userid, zzycomment, articleid,'参数');
    if (commentid ==0) {
      statement = `INSERT INTO comment(article_id,user_id,text) VALUES (?,?,?)`
      return  await connection.execute(statement,[articleid,userid,zzycomment])
    }else {
      statement =`INSERT INTO childrencomment(comment_id,user_id,text) VALUES (?,?,?)`
      return await connection.execute(statement,[commentid,userid,zzycomment])
    }
 
  }
}
module.exports = new pushcontent()